﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using DevExpress.XtraGrid.Views.Grid;
using E4RPDevExpress10;
using E4RPPrintManager10;
using E4RPTools;
using StationCommon;
using StationExternal;

namespace Station
{
    public class Controller
    {
        private SqlConnection _connection;

        private const string PriPro750 = "PRIMAVERAPRO750";
        private const string PrimaveraPro7 = "PRIMAVERAPRO7";
        private const string PrimaveraExpress6 = "PRIMAVERAEXPRESS6";

        #region Public Events

        public event EventHandler<EventArgs> WorkSheetShow;
        
        public event EventHandler<EventArgs> WorkSheetSaved;

        public event EventHandler<EventArgs> VehicleSaved;

        public event EventHandler<EventArgs> MechanicSaved;

        #endregion Public Events

        #region Properties

        public FolhaObraDataSet WorkOrder { get; set; }

        public IWin32Window OwnerWindow { get; set; }

        #endregion Properties

        #region Private Methods

        private void OnWorkSheetShow()
        {
            EventHandler<EventArgs> workSheetShow = WorkSheetShow;
            if (workSheetShow != null)
                workSheetShow(this, new EventArgs());
        }

        private void OnWorkSheetSaved()
        {
            EventHandler<EventArgs> workSheetSaved = WorkSheetSaved;
            if (workSheetSaved != null)
                workSheetSaved(this, new EventArgs());
        }
        
        private void OnVehicleSaved()
        {
            EventHandler<EventArgs> vehicleSaved = VehicleSaved;
            if (vehicleSaved != null)
                vehicleSaved(this, new EventArgs());
        }

        private void OnMechanicSaved()
        {
            EventHandler<EventArgs> mechanicSaved = MechanicSaved;
            if (mechanicSaved != null)
                mechanicSaved(this, new EventArgs());
        }

        private static Result IsVehicleFieldsValid(VeiculoDataSet.VeiculoRow vehicle)
        {
            var result = new Result();
            if (string.IsNullOrEmpty(vehicle.Matricula)) result.Add("Matricula", "Por favor, preencha a matrícula.");
            if (vehicle.IsNomeClienteNull() || vehicle.NomeCliente == "")  result.Add("ClienteNome", "Por favor, preencha o cliente.");
            if (vehicle.IsIdClienteNull() || vehicle.IdCliente  == "") result.Add("ClienteIdExterno", "Por favor, preencha o cliente.");
            //if (vehicle.IsMarcaNull() || vehicle.Marca == "") result.Add("Marca", "Por favor, preencha a marca.");
            //if (vehicle.IsModeloNull() || vehicle.Modelo == "") result.Add("Modelo", "Por favor, preencha o modelo.");
            //if (vehicle.IsTipoViaturaNull() || vehicle.TipoViatura == "") result.Add("TipoViatura", "Por favor, preencha o tipo de viatura.");
            //if (vehicle.IsCavalosPotenciaNull() || vehicle.CavalosPotencia == "") result.Add("CavalosPotencia", "Por favor, preencha os cavalos de potencia.");
            //if (vehicle.IsCilindradaNull() || vehicle.Cilindrada == "") result.Add("Cilindrada", "Por favor, preencha a cilindrada.");
            //if (vehicle.IsTipoCombustivelNull() || vehicle.TipoCombustivel == "") result.Add("Combustivel", "Por favor, preencha o combustivel.");
            //if (vehicle.IsNumeroChassisNull() || vehicle.NumeroChassis == "") result.Add("NumeroChassis", "Por favor, preencha o número de chassis.");
            //if (vehicle.IsMedidaPneusNull() || vehicle.MedidaPneus == "") result.Add("MedidaPneus", "Por favor, preencha o medida dos pneus.");
            //if (vehicle.IsDataFabricoNull() || vehicle.DataFabrico == DateTime.MinValue) result.Add("DataFabrico", "Por favor, preencha a data de fabrico.");
            //if (vehicle.IsDataMatriculaNull() || vehicle.DataMatricula == DateTime.MinValue) result.Add("DataMatricula", "Por favor, preencha a data de matrícula.");
            //if (vehicle.IsKmsNull() || vehicle.Kms.ToString() == "") result.Add("Kms", "Por favor, preencha o número de kms.");
            return result;
        }

        private Result ExistsRegistration(VeiculoDataSet.VeiculoRow vehicle)
        {
            var result = new Result();
            if(vehicle.RowState != DataRowState.Added)
                return result;            
            var vehicleResult = GetVehicle(vehicle.Matricula);
            if (vehicleResult.Veiculo.Count != 0)
                result.Add("Matricula", "Já existe uma matrícula registada com o mesmo número.");
            return result;
        }

        private FolhaObraDataSet.ItemFolhaObraRow GetWorkOrderItem(long itemId)
        {
            foreach (FolhaObraDataSet.ItemFolhaObraRow workSheetItem in WorkOrder.ItemFolhaObra)
            {
                if (workSheetItem.RowState == DataRowState.Deleted)
                    continue;
                if (workSheetItem.IdItem == itemId)
                    return workSheetItem;
            }
            return null;
        }

        private long GetNextWorkSheetItemId()
        {
            long itemId = 0;
            foreach (FolhaObraDataSet.ItemFolhaObraRow workSheetItem in WorkOrder.ItemFolhaObra)
            {
                if (workSheetItem.RowState == DataRowState.Deleted)
                    continue;
                if (itemId > workSheetItem.IdItem)
                    itemId = workSheetItem.IdItem;
            }
            return itemId - 1;
        }

        private static Result IsWorkSheetFieldsValid(FolhaObraDataSet dataSet, bool bill)
        {
            var result = new Result();
            var workSheet = dataSet.FolhaObra[0];
            if (workSheet.IsNomeClienteNull() || workSheet.NomeCliente == "") result.Add("ClienteNome", "Por favor, preencha o cliente.");
            if (workSheet.IsIdClienteNull() || workSheet.IdCliente == "") result.Add("ClienteIdExterno", "Por favor, preencha o cliente.");
            if (workSheet.IsDataEntregaNull()) result.Add("DataEntrega", "Por favor, preencha a data de entrega.");
            if (workSheet.IsDataRecepcaoNull()) result.Add("DataRecepcao", "Por favor, preencha a data de recepção.");
            if (workSheet.IsMatriculaVeiculoNull() || workSheet.MatriculaVeiculo == "") result.Add("MatriculaVeiculo", "Por favor, preencha a matícula da viatura.");
            if (bill && dataSet.ItemFolhaObra.Count == 0)
                workSheet.RowError = "Para facturar uma folha de obra é necessário ter pelo menos um item.";
            foreach(FolhaObraDataSet.ItemFolhaObraRow workSheetItem in dataSet.ItemFolhaObra)
            {
                if (workSheetItem.RowState == DataRowState.Deleted)
                    continue;
                if (workSheetItem.IsReferenciaNull() || workSheetItem.Referencia == "")
                    workSheetItem.SetColumnError("Referencia", "Por favor, preencha o produto.");                
                if (bill && !workSheetItem.IsFacturaNull())
                    workSheetItem.SetColumnError("Factura", "Este item já foi facturado.");
                if (bill && (workSheetItem.IsIdClienteFacturaNull() || workSheetItem.IdClienteFactura == ""))
                    workSheetItem.SetColumnError("IdClienteFactura", "Por favor, preencha o cliente.");
                if (bill && (workSheetItem.IsNomeClienteFacturaNull() || workSheetItem.NomeClienteFactura == ""))
                    workSheetItem.SetColumnError("NomeClienteFactura", "Por favor, preencha o cliente.");
            }
            return result;
        }

        public bool ExistsWorkSheet(string number)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                var exists = Primavera.ExistsWorkOrderNumber(sqlTransaction, number);
                sqlTransaction.Commit();
                return exists;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                ExceptionPublisher.Publish(ConfigurationManager.AppSettings["LogFile"], e);
                ExceptionPublisher.ShowErrorMessage(OwnerWindow, "Não foi validar se existe folha de obra.", "Folha Obra");
                return false;
            }
        }
        
        private void PublishAndShowErrorMessage(Exception e, string errorMessage)
        {
            ExceptionPublisher.Publish(ConfigurationManager.AppSettings["LogFile"], e);
            ExceptionPublisher.ShowErrorMessage(OwnerWindow, errorMessage, "Folha Obra");
        }

        private FolhaObraDataSet GetWorkOrder(long workOrderId)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                FolhaObraDataSet dataSet = Primavera.GetWorkOrder(sqlTransaction, workOrderId);
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a folha de obra.");
                return new FolhaObraDataSet();
            }
        }

        private static Result IsMechanicFieldsValid(MecanicoDataSet.MecanicoRow mechanic)
        {
            var result = new Result();
            if (string.IsNullOrEmpty(mechanic.Nome)) result.Add("Nome", "Por favor, preencha o nome.");
            return result;
        }

        private void FixConnection()
        {
            if (_connection.State == ConnectionState.Open)
                _connection.Close();
            _connection.Open();
        }

        private static List<string> GetDistintClientsWithItemsToBill(FolhaObraDataSet workSheet)
        {
            var clients = new List<string>();
            foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in workSheet.ItemFolhaObra)
                if (!clients.Contains(workOrderItem.IdClienteFactura) && workOrderItem.Facturar && workOrderItem.Estado == WorkOrderItemState.Facturar.ToString())
                    clients.Add(workOrderItem.IdClienteFactura);
            return clients;
        }

        private static void UpdateClientWorkOrderItem(FolhaObraDataSet workOrder, string clientId, string billNumber)
        {
            foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in workOrder.ItemFolhaObra)
                if (workOrderItem.IdClienteFactura == clientId && workOrderItem.Facturar && workOrderItem.Estado == WorkOrderItemState.Facturar.ToString())
                {
                    workOrderItem.Factura = billNumber;
                    workOrderItem.Estado = WorkOrderItemState.Facturada.ToString();
                }
        }

        private SaveResult SaveClientPrimaveraWorkOrder(SqlTransaction sqlTransaction, FolhaObraDataSet workOrder, string clientId)
        {
            var items = new Collection<StationExternalItem>();
            foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in workOrder.ItemFolhaObra)
            {
                if (workOrderItem.IdClienteFactura != clientId || !workOrderItem.Facturar || workOrderItem.Estado != WorkOrderItemState.Facturar.ToString())
                    continue;
                var stationExternalItem = new StationExternalItem(workOrder.FolhaObra[0].DataEntrega, workOrderItem.Referencia, 
                    workOrderItem.IsDescricaoNull() ? "" : workOrderItem.Descricao, workOrderItem.Quantidade,
                    workOrderItem.ValorBase, workOrderItem.ValorUnitario, workOrderItem.PercentagemDesconto, workOrderItem.ValorDesconto,
                    workOrderItem.ValorSemIva, workOrderItem.PercentagemIva, Convert.ToDecimal(workOrderItem.ValorComIva - workOrderItem.ValorSemIva),
                    Convert.ToDecimal(workOrderItem.ValorComIva), workOrderItem.Factura, workOrderItem.IdClienteFactura,
                    workOrderItem.NomeClienteFactura, workOrder.FolhaObra[0].Observacoes);
                items.Add(stationExternalItem);
            }
            SaveResult result;
            switch (ManagementSoftware.ToUpper())
            {
                case PriPro750:
                    result = PrimaveraPro750.SaveBill(sqlTransaction, items);
                    break;
                case PrimaveraPro7:
                    result = PrimaveraPro.SaveBill(sqlTransaction, items);
                    break;
                case PrimaveraExpress6:
                    result = PrimaveraExpress.SaveBill(sqlTransaction, items);
                    break;
                default:
                    result = new SaveResult(false, "", "Software gestão não suporta gravação.");
                    break;
            }
            if (result.OK)
                UpdateClientWorkOrderItem(workOrder, clientId, result.BillNumber);
            return result;
        }

        private SaveResult SavePrimaveraWorkOrder(SqlTransaction sqlTransaction, FolhaObraDataSet workSheet)
        {
            var clients = GetDistintClientsWithItemsToBill(workSheet);
            SaveResult result;
            foreach (string client in clients)
            {
                result = SaveClientPrimaveraWorkOrder(sqlTransaction, workSheet, client);
                if (!result.OK)
                    return result;
            }
            return SaveResult.Sucess("");            
        }

        private SaveResult SaveExternalWorkOrder(SqlTransaction sqlTransaction, FolhaObraDataSet workSheet)
        {
            return SavePrimaveraWorkOrder(sqlTransaction, workSheet);            
        }
      
        private static void UpdateWorkOrderTotals(FolhaObraDataSet dataSet)
        {
            dataSet.FolhaObra[0].Valor = 0;
            dataSet.FolhaObra[0].ValorDesconto = 0;
            dataSet.FolhaObra[0].ValorSemIva = 0;
            dataSet.FolhaObra[0].ValorIva = 0;
            dataSet.FolhaObra[0].ValorComIva = 0;
            foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in dataSet.ItemFolhaObra)
            {
                if (workOrderItem.RowState == DataRowState.Deleted)
                    continue;
                dataSet.FolhaObra[0].Valor += workOrderItem.ValorUnitario;
                dataSet.FolhaObra[0].ValorDesconto += workOrderItem.ValorDesconto;
                dataSet.FolhaObra[0].ValorSemIva += workOrderItem.ValorSemIva;
                dataSet.FolhaObra[0].ValorIva += workOrderItem.ValorIva;
                dataSet.FolhaObra[0].ValorComIva += workOrderItem.ValorComIva;
            }
        }

        private static void UpdateWorkOrderState(FolhaObraDataSet workOrder)
        {
            workOrder.FolhaObra[0].Estado = WorkOrderState.ADecorrer.ToString();
            if (workOrder.ItemFolhaObra.Count == 0)
                return;
            foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in workOrder.ItemFolhaObra)
                if (workOrderItem.Estado != WorkOrderItemState.Facturada.ToString())
                    return;
            workOrder.FolhaObra[0].Estado = WorkOrderState.Facturada.ToString();
        }

        private static void SaveWorkOrder(SqlTransaction sqlTransaction, FolhaObraDataSet workOrder)
        {
            Primavera.SaveWorkOrder(sqlTransaction, workOrder.FolhaObra[0]);
            Primavera.UpdateWorkOrderItems(sqlTransaction, workOrder.ItemFolhaObra);
        }

        private static void UpdateVehicleKms(SqlTransaction sqlTransaction, FolhaObraDataSet workOrder)
        {
            Primavera.UpdateVehicleKms(sqlTransaction, workOrder.FolhaObra[0]);            
        }

        private ListaLayoutDataSet GetListLayouts(string list)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                ListaLayoutDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:                    
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetListLayouts(sqlTransaction, list);
                        break;
                    default:
                        dataSet = new ListaLayoutDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de layouts da lista.");
                return new ListaLayoutDataSet();
            }
        }

        private static List<string> GetLayouts(ListaLayoutDataSet dataSet)
        {
            var layouts = new List<string>();
            foreach (var layout in dataSet.ListaLayout)
                layouts.Add(layout.Nome);
            return layouts;
        }

        private void SaveLayout(string lista, string nome, string xmlLayout)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        Primavera.SaveLayout(sqlTransaction, lista, nome, xmlLayout);
                        break;
                    default:
                        break;
                }
                sqlTransaction.Commit();                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gravar layout da lista.");                
            }
        }

        private static string GetPrinterName()
        {
            string printerName = ConfigurationManager.AppSettings["PrinterName"];
            if (!string.IsNullOrEmpty(printerName))
                return printerName;
            var printManager = new PrintManager();
            return printManager.GetDefaultPrinterName();
        }

        #endregion Private Methods

        public bool Login(string userName, string password)
        {
            string dataSource = ConfigurationManager.AppSettings["FonteDados"];
            string databaseName = ConfigurationManager.AppSettings["NomeBaseDados"];
            var connectionString = "Data Source=" + dataSource + ";Initial Catalog=" + databaseName + ";User ID=" + userName +
                   ";Password=" + password + ";MultipleActiveResultSets=True;";                
            try
            {
                _connection = new SqlConnection(connectionString);
                _connection.Open();
            }
            catch (Exception e)
            {
                PublishAndShowErrorMessage(e, "Não foi possível ligar à base de dados.");
                return false;
            }
            return true;
        }

        public bool IsManagementSoftwareValid()
        {
            var managementSoftware = ConfigurationManager.AppSettings["SoftwareGestao"];
            switch (managementSoftware.ToUpper())
            {
                case PriPro750:
                case PrimaveraPro7:
                case PrimaveraExpress6:
                    return true;
                default:
                    PublishAndShowErrorMessage(new Exception(), string.Format("Software gestão '{0}' não é suportado pela aplicação.", managementSoftware));
                    return false;
            }
        }

        public string ManagementSoftware { set; get; }

        public VeiculoDataSet Vehicles()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                VeiculoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetVehicles(sqlTransaction);
                        break;
                    default:
                        dataSet = new VeiculoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de veículos.");
                return new VeiculoDataSet();
            }
        }

        public ClienteDataSet Clients()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                ClienteDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                        dataSet = PrimaveraPro750.GetClients(sqlTransaction);
                        break;
                    case PrimaveraPro7:
                        dataSet = PrimaveraPro.GetClients(sqlTransaction);
                        break;
                    case PrimaveraExpress6:
                        dataSet = PrimaveraExpress.GetClients(sqlTransaction);
                        break;
                    default:
                        dataSet = new ClienteDataSet();
                        break;
                }
                dataSet.Cliente.AddClienteRow("", "", "");
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de clientes.");
                return new ClienteDataSet();
            }
        }

        public PaisDataSet Countries()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                PaisDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetCountries(sqlTransaction);
                        break;
                    default:
                        dataSet = new PaisDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de países.");
                return new PaisDataSet();
            }
        }

        public TipoVeiculoDataSet VehicleTypes()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                TipoVeiculoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetVehicleTypes(sqlTransaction);
                        break;
                    default:
                        dataSet = new TipoVeiculoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de tipo de veículo.");
                return new TipoVeiculoDataSet();
            }
        }

        public TipoCombustivelDataSet FuelTypes()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                TipoCombustivelDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetFuelTypes(sqlTransaction);
                        break;
                    default:
                        dataSet = new TipoCombustivelDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de tipos de combustível.");
                return new TipoCombustivelDataSet();
            }
        }

        public VeiculoDataSet GetVehicle(string plate)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                VeiculoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetVehicle(sqlTransaction, plate);
                        break;
                    default:
                        dataSet = new VeiculoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler o veículo.");
                return new VeiculoDataSet();
            }
        }

        public Result IsVehicleValid(VeiculoDataSet.VeiculoRow vehicle)
        {
            var result = IsVehicleFieldsValid(vehicle);
            return result.Failed ? result : ExistsRegistration(vehicle);
        }

        public void SaveVehicle(VeiculoDataSet.VeiculoRow vehicle)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        Primavera.SaveVehicle(sqlTransaction, vehicle);
                        break;
                    default:
                        break;
                }
                sqlTransaction.Commit();
                OnVehicleSaved();                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gravar o veículo.");
            }
        }

        public ProdutoDataSet Products()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                ProdutoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                        dataSet = PrimaveraPro750.GetProducts(sqlTransaction);
                        break;
                    case PrimaveraPro7:
                        dataSet = PrimaveraPro.GetProducts(sqlTransaction);
                        break;
                    case PrimaveraExpress6:
                        dataSet = PrimaveraExpress.GetProducts(sqlTransaction);
                        break;
                    default:
                        dataSet = new ProdutoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de produtos.");                
                return new ProdutoDataSet();                
            }
        }

        public FolhaObraDataSet WorkSheets()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                FolhaObraDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetWorkOrders(sqlTransaction);
                        break;
                    default:
                        dataSet = new FolhaObraDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de folhas de obra.");
                return new FolhaObraDataSet();
            }
        }

        public void ShowWorkSheet(long workOrderId)
        {
            WorkOrder = GetWorkOrder(workOrderId);            
            OnWorkSheetShow();
        }

        public void NewWorkSheet()
        {
            NewWorkSheet(DateTime.Today.AddHours(9), DateTime.Today.AddHours(17));            
        }

        public void NewWorkSheet(DateTime reception, DateTime delivery)
        {
            WorkOrder = new FolhaObraDataSet();
            WorkOrder.FolhaObra.AddFolhaObraRow(-1, reception, delivery, reception.Year, -1, "", 0, "", "", 0, 0, 0, 0, 0, WorkOrderState.ADecorrer.ToString(), "", "");
            OnWorkSheetShow();
        }

        public FolhaObraDataSet.ItemFolhaObraRow AddWorkOrderItem()
        {
            var workOrderItem = WorkOrder.ItemFolhaObra.NewItemFolhaObraRow();
            workOrderItem.Id = WorkOrder.FolhaObra[0].Id;
            workOrderItem.IdItem = GetNextWorkSheetItemId();
            workOrderItem.Referencia = "";
            workOrderItem.Descricao = "";
            workOrderItem.Quantidade = 1;
            workOrderItem.ValorUnitario = 0;
            workOrderItem.ValorBase = 0;
            workOrderItem.PercentagemDesconto = 0;
            workOrderItem.ValorDesconto = 0;
            workOrderItem.ValorSemIva = 0;
            workOrderItem.PercentagemIva = 0;
            workOrderItem.ValorIva = 0;
            workOrderItem.ValorComIva = 0;
            workOrderItem.Facturar = true;
            workOrderItem.Estado = WorkOrderItemState.Facturar.ToString();
            if (WorkOrder.FolhaObra[0].NomeCliente != "")
            {
                workOrderItem.IdClienteFactura = WorkOrder.FolhaObra[0].IdCliente;
                workOrderItem.NomeClienteFactura = WorkOrder.FolhaObra[0].NomeCliente;
            }
            WorkOrder.ItemFolhaObra.AddItemFolhaObraRow(workOrderItem);
            return workOrderItem;
        }

        public void UpdateWorkSheetItem(FolhaObraDataSet.ItemFolhaObraRow workOrderItem)
        {
            FolhaObraDataSet.ItemFolhaObraRow workSheetItem = GetWorkOrderItem(workOrderItem.IdItem);
            if (workOrderItem.IsReferenciaNull()) workSheetItem.SetReferenciaNull(); else workSheetItem.Referencia = workOrderItem.Referencia;
            if (workOrderItem.IsDescricaoNull()) workSheetItem.SetDescricaoNull(); else workSheetItem.Descricao = workOrderItem.Descricao;
            workSheetItem.Quantidade = workOrderItem.Quantidade;
            workSheetItem.ValorUnitario = workOrderItem.ValorUnitario;
            workSheetItem.ValorBase = workOrderItem.ValorBase;
            workSheetItem.PercentagemDesconto = workOrderItem.PercentagemDesconto;
            workSheetItem.ValorDesconto = workOrderItem.ValorDesconto;
            workSheetItem.ValorSemIva = workOrderItem.ValorSemIva;
            workSheetItem.PercentagemIva = workOrderItem.PercentagemIva;
            workSheetItem.ValorIva = workOrderItem.ValorIva;
            workSheetItem.ValorComIva = workOrderItem.ValorComIva;
            if (workOrderItem.IsIdMecanicoNull()) workSheetItem.SetIdMecanicoNull(); else workSheetItem.IdMecanico = workOrderItem.IdMecanico;
            if (workOrderItem.IsFacturaNull()) workSheetItem.SetFacturaNull(); else workSheetItem.Factura = workOrderItem.Factura;
            if (workOrderItem.IsIdClienteFacturaNull()) workSheetItem.SetIdClienteFacturaNull(); else workSheetItem.IdClienteFactura = workOrderItem.IdClienteFactura;
            if (workOrderItem.IsNomeClienteFacturaNull()) workSheetItem.SetNomeClienteFacturaNull(); else workSheetItem.NomeClienteFactura = workOrderItem.NomeClienteFactura;
            workSheetItem.Facturar = workOrderItem.Facturar;
            workSheetItem.Estado = workOrderItem.Estado;
        }

        public void RemoveWorkSheetItem(FolhaObraDataSet.ItemFolhaObraRow workOrderItem)
        {
            FolhaObraDataSet.ItemFolhaObraRow workSheetItem = GetWorkOrderItem(workOrderItem.IdItem);
            workSheetItem.Delete();
        }

        public bool ExistsOpenedWorkSheet(IWin32Window owner, string action)
        {
            if (WorkOrder.ItemFolhaObra.Count == 0)
                return true;
            DialogResult result = MessageBox.Show(owner, "Existem folhas de obra por tratar." + Environment.NewLine + action,
                "Nova Folha Obra", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2);
            return result == DialogResult.Yes;
        }

        public Result IsWorkSheetValid(bool bill)
        {
            return IsWorkSheetFieldsValid(WorkOrder, bill);
        }

        public void SaveWorkOrder(IWin32Window owner, bool bill)
        {
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                if (bill)
                {
                    SaveResult result = SaveExternalWorkOrder(sqlTransaction, WorkOrder);
                    if (!result.OK)
                    {
                        MessageBox.Show(owner, "Não foi possível gravar a factura em Primavera." + Environment.NewLine + result.Error, 
                            "Folha Obra", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1);
                        sqlTransaction.Rollback();
                        return;
                    }                    
                }
                UpdateWorkOrderTotals(WorkOrder);
                UpdateWorkOrderState(WorkOrder);
                SaveWorkOrder(sqlTransaction, WorkOrder);
                UpdateVehicleKms(sqlTransaction, WorkOrder);
                sqlTransaction.Commit();
                OnWorkSheetSaved();
                NewWorkSheet();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gravar a folha de obra.");
            }            
        }

        public void CloseConnection()
        {
            _connection.Close();
        }

        public void LockWorkOrder()
        {
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                WorkOrder.FolhaObra[0].Estado = WorkOrderState.Encerrada.ToString();
                foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in WorkOrder.ItemFolhaObra)
                    workOrderItem.Estado = WorkOrderItemState.Bloqueada.ToString();
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        Primavera.UpdateWorkOrderState(sqlTransaction, WorkOrder.FolhaObra[0].Id, WorkOrderState.Encerrada.ToString());
                        Primavera.UpdateWorkOrderItemsState(sqlTransaction, WorkOrder.FolhaObra[0].Id, WorkOrderItemState.Bloqueada.ToString());
                        break;
                    default:
                        break;
                }
                sqlTransaction.Commit();
                OnWorkSheetSaved();                
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível bloquear a folha de obra.");
            }
        }

        public void UnLockWorkOrder()
        {
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                WorkOrder.FolhaObra[0].Estado = WorkOrderState.ADecorrer.ToString();
                foreach (FolhaObraDataSet.ItemFolhaObraRow workOrderItem in WorkOrder.ItemFolhaObra)
                    workOrderItem.Estado = WorkOrderItemState.Facturar.ToString();
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        Primavera.UpdateWorkOrderState(sqlTransaction, WorkOrder.FolhaObra[0].Id, WorkOrderState.ADecorrer.ToString());
                        Primavera.UpdateWorkOrderItemsState(sqlTransaction, WorkOrder.FolhaObra[0].Id, WorkOrderItemState.Facturar.ToString());
                        break;
                    default:
                        break;
                }
                sqlTransaction.Commit();
                OnWorkSheetSaved();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível desbloquear a folha de obra.");                
            }
        }

        public RelatorioDataSet Reports()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                RelatorioDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetReports(sqlTransaction);
                        break;
                    default:
                        dataSet = new RelatorioDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de relatorios.");
                return new RelatorioDataSet();
            }
        }
      
        public void Print(string reportFile, bool preview)
        {
            var printManager = new PrintManager
            {
                NumberOfCopies = 1,
                PrinterName = GetPrinterName(),
                ReportFile = reportFile,                
            };
            var user = ConfigurationManager.AppSettings["User"];
            var password = ConfigurationManager.AppSettings["Password"];
            var server = ConfigurationManager.AppSettings["FonteDados"];
            var database = ConfigurationManager.AppSettings["NomeBaseDados"]; 
            if(preview)
                printManager.PrintPreviewDatabaseLogon(user, password, server, database);
            else
                printManager.PrintDatabaseLogon(user, password, server, database);            
        }

        public MecanicoDataSet Mechanics()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                MecanicoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetMechanics(sqlTransaction);
                        break;
                    default:
                        dataSet = new MecanicoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de mecânicos.");
                return new MecanicoDataSet();
            }
        }

        public MecanicoDataSet GetMechanic(long mechanicId)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                MecanicoDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetMechanic(sqlTransaction, mechanicId);
                        break;
                    default:
                        dataSet = new MecanicoDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler o mecânico.");
                return new MecanicoDataSet();
            }
        }

        public Result IsMechanicValid(MecanicoDataSet.MecanicoRow mechanic)
        {
            return IsMechanicFieldsValid(mechanic);            
        }

        public void SaveMechanic(MecanicoDataSet.MecanicoRow mechanic)
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        Primavera.SaveMechanic(sqlTransaction, mechanic);
                        break;
                    default:
                        break;
                }
                sqlTransaction.Commit();
                OnMechanicSaved();
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível gravar o mecânico.");
            }
        }

        public ListaItemFolhaObraDataSet GetWorkOrderItemList()
        {
            FixConnection();
            var sqlTransaction = _connection.BeginTransaction();
            try
            {
                ListaItemFolhaObraDataSet dataSet;
                switch (ManagementSoftware.ToUpper())
                {
                    case PriPro750:
                    case PrimaveraPro7:
                    case PrimaveraExpress6:
                        dataSet = Primavera.GetWorkOrderItemList(sqlTransaction);
                        break;
                    default:
                        dataSet = new ListaItemFolhaObraDataSet();
                        break;
                }
                sqlTransaction.Commit();
                return dataSet;
            }
            catch (Exception e)
            {
                sqlTransaction.Rollback();
                PublishAndShowErrorMessage(e, "Não foi possível ler a lista de items de folha de obra.");
                return new ListaItemFolhaObraDataSet();
            }
        }

        public string LoadLayout(GridView gridView, string list)
        {
            try
            {
                ListaLayoutDataSet dataSet = GetListLayouts(list);
                var form = new LoadLayoutXtraForm {Layouts = GetLayouts(dataSet)};
                if (form.ShowDialog() != DialogResult.OK)
                    return "";
                string xmlLayout = dataSet.ListaLayout.FindByListaNome(list, form.LayoutName).XmlLayout;
                DevExpressHelper.RestoreLayoutFromXml(gridView, xmlLayout); 
                return form.LayoutName;
            }
            catch(Exception) 
            {
                return "";
            }
        }

        public string SaveLayout(IWin32Window owner, string list, string xmlLayout)
        {
            try
            {
                var dataSet = GetListLayouts(list);
                var form = new SaveLayoutXtraForm {OwnerWindow = owner, Layouts = GetLayouts(dataSet)};
                if (form.ShowDialog() != DialogResult.OK)
                    return "";
                SaveLayout(list, form.LayoutName, xmlLayout);
                return form.LayoutName;
            }
            catch(Exception)
            {
                return "";
            }
        }

        public void PrintWorkOrder()
        {
            var printManager = new PrintManager
                                   {
                                       NumberOfCopies = 1,
                                       PrinterName = GetPrinterName(),
                                       ReportFile = "FolhaObra.rpt",
                                       DataSource = WorkOrder
                                   };
            
            printManager.Print();
        }

        public void PrintPreviewWorkOrder()
        {
            var form = new PrintPreviewXtraForm();
            form.Setup(WorkOrder, "FolhaObra.rpt");
            form.ShowDialog();                
        }

//Relatório (carta) para o cliente para enviar por correio ou email a solicitar a revisão; 
//Relatório de actividade entre datas (o que foi feito em folhas de obra sobre os veículos por cliente entre datas); 

//O envio por email e impressão deve ser para todos.
    }
}
